// Package util
// @Title util[简述该包的主要作用]
// @Description
// @Author  cnHuaShao
// @since v0.0.1
// @Version v1.0.0
// @Date 2024/4/21 22:47
// @Update 2024/4/21 22:47
package util

import (
	"FinancialManagement/src/base/baselog"
	"FinancialManagement/src/base/db"
	"database/sql"
	"encoding/json"
	"fmt"
	"go.uber.org/zap"
	"log"
	"reflect"
	"strconv"
	"time"
	"unsafe"
)

var (
	ManipulateSQL  map[string]map[string]string
	DataSourceType map[int]string
)

const (
	DATABASE   = "database"
	TABLE      = "table"
	TABLEFIELD = "table_field"
	VIEW       = "view"
)

func init() {
	log.Println("-------- 初始化基础模块 --------")
	ManipulateSQL = make(map[string]map[string]string)
	DataSourceType = make(map[int]string)

	// 数据库类型
	DataSourceType = map[int]string{
		1: "MariaDB",
		2: "Postgres",
		3: "Oracle",
		4: "MySQL",
	}
	// mariadb操作相关SQL
	ManipulateSQL[DataSourceType[1]] = map[string]string{
		DATABASE: "SHOW DATABASES",
		// 查询所有表，模式名（库名）、表名、注释、行数
		TABLE: "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME", // 需携带参数 库名
		// 字段名、字段类型、字段注释
		TABLEFIELD: "SELECT COLUMN_NAME,COLUMN_TYPE,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name=? AND table_schema=?", // 需携带参数 表名、库名
		VIEW:       "",
	}

	// Postgres操作相关SQL
	ManipulateSQL[DataSourceType[2]] = map[string]string{
		DATABASE: "SELECT datname FROM pg_database",
		// 查询所有表，模式名、表名、注释、行数
		TABLE: "select (select pn.nspname from pg_namespace pn WHERE pn.oid=c.relnamespace) as nspname,c.relname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment,reltuples from pg_class c WHERE c.relkind = 'r'",
		// 字段名、字段类型、字段注释
		TABLEFIELD: "select a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)')) as type,d.description from pg_class c,pg_attribute a,pg_type t,pg_description d where c.relname=? and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum",
		VIEW:       "select (select pn.nspname from pg_namespace pn WHERE pn.oid=c.relnamespace) as nspname,c.relname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment,reltuples from pg_class c WHERE c.relkind ='v'",
	}

	// Oracle操作相关SQL
	ManipulateSQL[DataSourceType[3]] = map[string]string{
		DATABASE:   "select * from v$tablespace",
		TABLE:      "",
		TABLEFIELD: "",
		VIEW:       "",
	}

	// MySQL操作相关SQL
	ManipulateSQL[DataSourceType[4]] = map[string]string{
		DATABASE: "SHOW DATABASES",
		// 查询所有表，模式名（库名）、表名、注释、行数
		TABLE: "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME", // 需携带参数 库名
		// 字段名、字段类型、字段注释
		TABLEFIELD: "SELECT COLUMN_NAME,COLUMN_TYPE,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name=? AND table_schema=?", // 需携带参数 表名、库名
		VIEW:       "",
	}
}

// GetDataBaseSQL
// @Description: 根据数据源类型进行获取查询数据库的SQL
// @param dataSourceType
// @return sql
func GetDataBaseSQL(dataSourceType int) (sql string) {
	return ManipulateSQL[DataSourceType[dataSourceType]][DATABASE]
}

// GetTableSQL
// @Description: 根据数据源类型进行获取查询数据库所有表的SQL
// @param dataSourceType
// @return sql
func GetTableSQL(dataSourceType int) (sql string) {
	return ManipulateSQL[DataSourceType[dataSourceType]][TABLE]
}

// GetTableFieldSQL
// @Description: 根据数据源类型进行获取查询表中所有字段的SQL
// @param dataSourceType
// @return sql
func GetTableFieldSQL(dataSourceType int) (sql string) {
	return ManipulateSQL[DataSourceType[dataSourceType]][TABLEFIELD]
}

// ScanRows2map
// @Description: 解析返回值为数组集合
// @param rows
// @return []map[string]string
func ScanRows2map(rows *sql.Rows) []map[string]interface{} {
	currentTime := time.Now().UnixNano()
	res := make([]map[string]interface{}, 0)          //  定义结果 map
	colTypes, _ := rows.ColumnTypes()                 // 返回的列信息
	var rowParam = make([]interface{}, len(colTypes)) // 传入到 rows.Scan 的参数 数组 用于存储返回字段名称数组
	var rowValue = make([]interface{}, len(colTypes)) // 接收数据一行列的数组 用于存储每行数据，和列数量相同，

	for i, colType := range colTypes {
		// baselog.Logger.Debug(colType.ScanType().String())
		// baselog.Logger.Debug(colType.DatabaseTypeName(), zap.String("scanType:", colType.ScanType().String()))

		rowValue[i] = reflect.New(colType.ScanType())           // 跟据数据库参数类型，创建默认值 和类型
		rowParam[i] = reflect.ValueOf(&rowValue[i]).Interface() // 跟据接收的数据的类型反射出值的地址
	}
	// 遍历
	for rows.Next() {
		rows.Scan(rowParam...)                 // 赋值到 rowValue 中
		record := make(map[string]interface{}) // 用于存储单行数据
		for i, colType := range colTypes {
			// baselog.Logger.Debug(colType.Name(), zap.String("column:", reflect.TypeOf(rowValue[i]).String()))
			if rowValue[i] == nil {
				record[colType.Name()] = ""
			} else {
				// baselog.Logger.Debug(colType.Name(), zap.String("col:", Byte2Str(rowValue[i].([]byte))))
				switch rowValue[i].(type) {
				case string:
					record[colType.Name()] = rowValue[i].(string)
				case int32:
					op, _ := rowValue[i].(int32)
					record[colType.Name()] = op
				case int64:
					op, _ := rowValue[i].(int64)
					record[colType.Name()] = op
				case time.Time:
					record[colType.Name()] = rowValue[i].(time.Time)
				case []uint8:
					record[colType.Name()] = string(rowValue[i].([]uint8))
				default:
					record[colType.Name()] = rowValue[i].(string)
				}
			}
			if colType.Name() == "majorjson" || colType.Name() == "classjson" {
				if rowValue[i] != nil {
					jsonStr := "[" + Byte2Str(rowValue[i].([]byte)) + "]"
					var dat map[string]interface{}
					if err := json.Unmarshal([]byte(jsonStr), &dat); err != nil {
						baselog.Logger.Error("转移JSON错误：", zap.Error(err))
					}
					record[colType.Name()] = dat
				}
			}
		}
		res = append(res, record)
	}
	endTime := time.Now().UnixNano()
	fmt.Println("耗时：", endTime-currentTime)
	return res
}

// ScanRows2mapTypeCast
// @Description: 解析返回值为数组集合，类型转换为int
// @param rows
// @return []map[string]interface{}
func ScanRows2mapTypeCast(rows *sql.Rows) []map[string]interface{} {
	res := make([]map[string]interface{}, 0)          //  定义结果 map
	colTypes, _ := rows.ColumnTypes()                 // 返回的列信息
	var rowParam = make([]interface{}, len(colTypes)) // 传入到 rows.Scan 的参数 数组 用于存储返回字段名称数组
	var rowValue = make([]interface{}, len(colTypes)) // 接收数据一行列的数组 用于存储每行数据，和列数量相同，

	for i, colType := range colTypes {
		// baselog.Logger.Debug(colType.RcName())
		// baselog.Logger.Debug(colType.ScanType().String())
		rowValue[i] = reflect.New(colType.ScanType())           // 跟据数据库参数类型，创建默认值 和类型
		rowParam[i] = reflect.ValueOf(&rowValue[i]).Interface() // 跟据接收的数据的类型反射出值的地址
	}
	// 遍历
	for rows.Next() {
		err := rows.Scan(rowParam...)
		if err != nil {
			log.Println(err.Error())
		} // 赋值到 rowValue 中
		record := make(map[string]interface{}) // 用于存储单行数据
		for i, colType := range colTypes {

			if rowValue[i] == nil {
				record[colType.Name()] = ""
			} else {
				// baselog.Logger.Debug(colType.RcName(), zap.String("col:", Byte2Str(rowValue[i].([]byte))), zap.String("type", colType.DatabaseTypeName()), zap.String("typeName", reflect.TypeOf(rowValue[i]).String()))
				switch rowValue[i].(type) {
				case string:
					record[colType.Name()] = rowValue[i].(string)
				case int32:
					op, _ := rowValue[i].(int32)
					record[colType.Name()] = op
				case int64:
					op, _ := rowValue[i].(int64)
					record[colType.Name()] = op
				case time.Time:
					record[colType.Name()] = rowValue[i].(time.Time)
				default:
					record[colType.Name()] = rowValue[i].(string)
				}
			}
		}
		res = append(res, record)
	}
	return res
}

// Byte2Str
// @Description: []byte to string
// @param b
// @return string
func Byte2Str(b []byte) string {
	return *(*string)(unsafe.Pointer(&b))
}

// Byte2Int
// @Description: []byte to int
// @param b
// @return int
func Byte2Int(b []byte) int {
	i, _ := strconv.Atoi(Byte2Str(b))
	return i
}

func Byte2Float(b []byte) float64 {
	f, _ := strconv.ParseFloat(Byte2Str(b), 64)
	return f
}

// GetData
// @Description: 依据传入SQL进行获取数据
// @param sql
// @return []map[string]interface{}
// @return error
func GetData(sql string) ([]map[string]interface{}, error) {
	baselog.Logger.Debug(sql)
	rows, err := db.Res.DB.GetDbR().Debug().Raw(sql).Rows()
	if err != nil {
		baselog.Logger.Error(err.Error())
		return nil, err
	}
	// defer db.Res.DB.GetDbR().Rollback()
	res := ScanRows2map(rows)
	defer rows.Close()
	return res, nil
}

func GetTypeCastData(sql string) ([]map[string]interface{}, error) {
	baselog.Logger.Debug(sql)
	rows, err := db.Res.DB.GetDbR().Debug().Raw(sql).Rows()
	if err != nil {
		baselog.Logger.Error(err.Error())
		return nil, err
	}
	// defer db.Res.DB.GetDbR().Rollback()
	res := ScanRows2mapTypeCast(rows)
	defer rows.Close()
	return res, nil
}

// GetJSONData
// @Description: 获取JSON数据
// @param sql
// @return []map[string]interface{}
// @return error
func GetJSONData(sql string) (map[string]interface{}, error) {
	baselog.Logger.Debug(sql)
	rows, err := db.Res.DB.GetDbR().Debug().Raw(sql).Rows()
	if err != nil {
		baselog.Logger.Error(err.Error())
		return nil, err
	}
	jsonStr := ""
	colTypes, _ := rows.ColumnTypes() // 列信息
	for _, colType := range colTypes {
		baselog.Logger.Debug(colType.Name())
	}
	for rows.Next() {
		rows.Scan(&jsonStr)
	}
	var res map[string]interface{}
	if err = json.Unmarshal([]byte(jsonStr), &res); err != nil {
		baselog.Logger.Error("转移JSON错误：", zap.Error(err))
	}
	defer rows.Close()
	return res, nil
}

// GetCountData
// @Description: 依据SQL查询总行数公共函数
// @param sql
// @return int
// @return error
func GetCountData(sql string) (int, error) {
	baselog.Logger.Debug(sql)
	rows, err := db.Res.DB.GetDbR().Debug().Raw(sql).Rows()
	if err != nil {
		baselog.Logger.Error(err.Error())
		return 0, err
	}
	count := 0
	for rows.Next() {
		rows.Scan(&count)
	}
	defer rows.Close()
	return count, nil
}
